using CodeSmith.Engine;
using SchemaExplorer;
using System;
using System.Windows.Forms.Design;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using System.Xml;
using System.Xml.Serialization;
namespace Common
{
	public class CommonSqlCode : CodeTemplate
	{

		#region 时间处理函数
		public string GetNoon()
		{
			int hour=System.DateTime.Now.Hour;
			if(hour<5)
			{
				return "凌晨";
			}
			else if(hour>=5 && hour<8)
			{
				return "早上";
			}
			else if(hour>=8 && hour<12)
			{
				return "上午";
			}
			else if(hour>=12 && hour<14)
			{
				return "中午";
			}
			else if(hour>=14 && hour<18 )
			{
				return "下午";
			}
			else if(hour>=18 && hour<23)
			{
			  return "晚上";	
			}
			else
			{
			  return "深夜";	
			}
		}
		
		public   string WeekDayOfCN()
        {
            string[] arrCnNames = new string[] { "日", "一", "二", "三", "四", "五", "六" };
            return "星期" + arrCnNames[(int)System.DateTime.Now.DayOfWeek];
        }
		
		public   string GetTimeCN()
		{
			return  System.DateTime.Now.ToLongDateString()+" "+WeekDayOfCN()+" "+GetNoon()+" " +System.DateTime.Now.ToLongTimeString();
		}
		#endregion

        #region 字符串处理
        //首字母小写的样式
        public string GetCamelCaseName(string name)
        {
            if (name.Equals(name.ToUpper()) && name.IndexOf("_") == -1)
                return name.ToLower().Replace(" ", "");
            else
            {
                // first get the PascalCase version of the name (DRY)
                string pascalName = GetPascalCaseName(name);
                // now lowercase the first character to transform it to camelCase
                return pascalName.Substring(0, 1).ToLower() + pascalName.Substring(1);
            }
        }

        /// <summary>
        /// Get the Pascal cased version of a name.  
        /// </summary>
        /// <param name="name">Name to be changed</param>
        /// <returns>PascalCased version of the name</returns>
		 //首字母大写的样式
        public string GetPascalCaseName(string name)
        {
			string[] splitNames;
			bool ChangeUnderscoreToPascalCase=false;
			if (ChangeUnderscoreToPascalCase)
			{
				char[] splitter =  {' '};
				splitNames = name.Split(splitter);
			}	
			else
			{
				char[] splitter = {'_', ' '};
				splitNames = name.Split(splitter);
			}
			
            string pascalName = "";
            foreach (string s in splitNames)
            {
                if (s.Length > 0)
                    pascalName += s.Substring(0, 1).ToUpper() + s.Substring(1).ToLower();
					
            }

            return pascalName;
        }
		#endregion
			
		#region 返回表名
		//返回首字母大写的表名
        public string GetTableName(string str)
		{
			//str=str.Substring(6);
			//int len=str.LastIndexOf('_');
			//if(len>0)
			//{
			//str=str.Substring(0,len);
			//}
			return GetPascalCaseName(str);
		}
						
		//返回首字母大写的表名
		public string GetTableName(TableSchema srcTable)
		{
		 	return GetTableName(srcTable.Name);
		}
		
		//返回首字母小写的表名
		public string GetLowTableName(string str)
		{
			string tt=GetTableName(str);
			if(tt.Length>0)
			{
			tt=tt.Substring(0,1).ToLower()+tt.Substring(1);
			}
		    return tt;
			
		}
		
		//返回首字母小写的表名
		public string GetLowTableName(TableSchema srcTable)
		{
			return GetLowTableName(srcTable.Name);
			
		}
		
		//返回缩写的表名
		public string GetShTableName(TableSchema srcTable)
		{
			string tt=GetFirstPrimaryColName(srcTable);
			return tt.Substring(0,tt.Length-2);	//剔除Id两个字		
		}
		
		//返回首字母小写缩写的表名
		public string GetShLowTableName(TableSchema srcTable)
		{
			return GetCamelCaseName(GetShTableName(srcTable));	 	
		}
		
		//返回全大写缩写的表名
		public string GetShBigTableName(TableSchema srcTable)
		{
			string tt=GetShTableName(srcTable); 
			return tt.ToUpper();		    
		}
		

		
		public string GetTableDesc(TableSchema srcTable)
		{
			string str=srcTable.Description;
			if(str.IndexOf("表名：")!=-1)
			{
				str=str.Substring(3);
			}
			if(str.LastIndexOf("信息表")!=-1)
			{
				str=str.Substring(0,str.Length-5);
			}
		    if(str.LastIndexOf("表")!=-1)
			{
				str=str.Substring(0,str.Length-1);
			}
			return str;
		}
		#endregion
		
		#region 返回各种类名或实例名		
		
		//返回实体层的类名
		public string GetEntityName(TableSchema srcTable)
		{
			return GetTableName(srcTable);			
		}
		
		//返回Dao的类名
		public string GetDaoName(TableSchema srcTable)
		{
			return GetTableName(srcTable)+"DAO";
		} 
		
		//返回Dao的类名
		public string GetLowDaoName(TableSchema srcTable)
		{
			return GetLowTableName(srcTable)+"DAO";
		} 
		
		//返回Action的类名
		public string GetActName(TableSchema srcTable,string pagetype)
		{
			return GetTableName(srcTable)+pagetype+"Act";
			
		}
		
		//返回Action类的实例名（首字母小写样式）
		public string GetLowActName(TableSchema srcTable,string pagetype)
		{
			return GetLowTableName(srcTable)+pagetype+"Act";
			
		}
	
		//返回Form的类名
		public string GetFormName(TableSchema srcTable,string pagetype)
		{
			return GetTableName(srcTable)+pagetype+"Form";
			
		}	
		
		//返回Form的类名（首字母小写样式）
		public string GetLowFormName(TableSchema srcTable,string pagetype)
		{
		   return GetLowTableName(srcTable)+pagetype+"Form";
		}
		#endregion 
		
		#region  返回sql对应的java类型及前缀＼get方法名
		
		//返回sql类型对应的Java类型
		public string GetSqlDbType(DataObjectBase column)	
		{
			switch (column.NativeType)
			{			
				case "bigint": return "long";
				case "binary": return "byte[]";
				case "bit": return "boolean";
				case "char": return "String";
				case "datetime": return "Date";
				case "decimal": return "BigDecimal";
				case "float": return "double";
				//case "image": return "Image";
				case "int": return "int";
				case "money": return "double";//返回float
				case "nchar": return "String";
				case "ntext": return "byte[]";
				case "numeric": return "BigDecimal";
				case "nvarchar": return "String";
				case "real": return "float";
				case "smalldatetime": return "Time";
				case "smallint": return "short";
				case "smallmoney": return "float";
				case "sql_variant": return "Object";
				case "sysname": return "String";
				case "text": return "String";
				case "timestamp": return "Timestamp";
				case "tinyint": return "int";
				case "uniqueidentifier": return "String";
				case "varbinary": return "byte[]";
				case "varchar": return "String";
				default: return "__UNKNOWN__" + column.NativeType;
			}
		}
		
		//返回sql类型对应的Java类型
		public string GetSqlDbWrapperType(DataObjectBase column)	
		{
			switch (column.NativeType)
			{			
				case "bigint": return "Integer";
				case "binary": return "byte[]";
				case "bit": return "Boolean";
				case "char": return "String";
				case "datetime": return "Date";
				case "decimal": return "BigDecimal";
				case "float": return "Double";
				//case "image": return "Image";
				case "int": return "Integer";
				case "money": return "Double";//返回float
				case "nchar": return "String";
				case "ntext": return "byte[]";
				case "numeric": return "BigDecimal";
				case "nvarchar": return "String";
				case "real": return "Float";
				case "smalldatetime": return "Time";
				case "smallint": return "Integer";
				case "smallmoney": return "Float";
				case "sql_variant": return "Object";
				case "sysname": return "String";
				case "text": return "String";
				case "timestamp": return "Timestamp";
				case "tinyint": return "Integer";
				case "uniqueidentifier": return "String";
				case "varbinary": return "byte[]";
				case "varchar": return "String";
				default: return "__UNKNOWN__" + column.NativeType;
			}
		}
		
		//返回Java类型的前缀
		public string GetTypePre(string type)	
		{
			switch (type)
			{
				case "int": return "i";
				case "long": return "l";
				case "float": return "f";
				case "String": return "s";
				case "java.lang.String": return "s";
				case "double": return "d";
				case "char": return "c";
				case "boolean": return "b";
				case "byte[]": return "a";
				case "Object": return "o";
				case "object": return "o";
				case "byte": return "i";
				case "shout": return "i";
				default: return "__UNKNOWN__" + type;
			}
		}
		
		//返回Java类型的前缀
		public string GetTypePre(DataObjectBase column)	
		{
			switch (column.NativeType)
			{
				case "bigint": return "l"; //"long";
				case "binary": return "a";//"byte[]";
				case "bit": return "b";//"boolean";
				case "char": return "s";//"java.lang.String";
				case "datetime": return "date";//"java.sql.Date";
				//case "decimal": return "java.math.BigDecimal";
				case "float": return "d";//"double";
				//case "image": return "Image";
				case "int": return "i";//"int";
				case "money": return "d";//"double";//返回float
				case "nchar": return "s";//"java.lang.String";
				case "ntext": return "a";//"byte[]";
				//case "numeric": return "java.math.BigDecimal";
				case "nvarchar": return "s";//"java.lang.String";
				case "real": return "f";//"float";
				//case "smalldatetime": return "java.sql.Time";
				case "smallint": return "i";//"short";
				case "smallmoney": return "f";//"float";
				case "sql_variant": return "s";//"Object";
				case "sysname": return "s";//"java.lang.String";
				case "text": return "a";// "byte[]";
				//case "timestamp": return "java.sql.Timestamp";
				case "tinyint": return "i";//"byte";
				case "uniqueidentifier": return "s";//"java.lang.String";
				case "varbinary": return "a";//"byte[]";
				case "varchar": return "s";//"java.lang.String";
				default: return "__UNKNOWN__" + column.NativeType;
			}
		}
		
		///get 方法名
		public string GetterMethod(DataObjectBase column)	
		{
			switch (column.NativeType)
			{
				case "bigint": return "getLong";//"long";
				case "binary": return "getBytes";//"byte[]";
				case "bit": return "getBoolean";// "boolean";
				case "char": return "getString ";//"String";
				case "datetime": return "getString";//"Date";
				case "decimal": return "getBigDecimal";//"BigDecimal";
				case "float": return "getDouble";//"double";
				//case "image": return "Image";
				case "int": return "getInt";// "int"; 
				case "money": return "getDouble";//"double"; 
				case "nchar": return  "getString ";//"String";
				case "ntext": return  "getBytes";//"byte[]";
				case "numeric": return "getBigDecimal";//"BigDecimal";
				case "nvarchar": return  "getString ";//"String";
				case "real": return  "getFloat";//"float";
				case "smalldatetime": return "getTime";//"Time";
				case "smallint": return "getShort";//"short";
				case "smallmoney": return  "getFloat";//"float";
				case "sql_variant": return "getObject";//"Object";
				case "sysname": return  "getString ";//"String";
				case "text": return  "getBytes";//"byte[]";
				case "timestamp": return "getTimestamp";//"Timestamp";
				case "tinyint": return "getByte";//"byte";
				case "uniqueidentifier": return  "getString ";//"String";
				case "varbinary": return  "getBytes";//"byte[]";
				case "varchar": return  "getString ";//"String";
				default: return "__UNKNOWN__" + column.NativeType;
			}
		}
		
		
		public string GetColTypeParse(DataObjectBase column)	
		{
			switch (column.NativeType)
			{
				case "bigint": return "Long.parseLong";//"long";
				case "binary": return "(byte[])";//"byte[]";
				case "bit": return "Boolean.parseBoolean";// "boolean";
				case "char": return "";//"String";
				case "datetime": return "DateFormat.getDateInstance().parse";//"Date";
				//case "decimal": return "getBigDecimal";//"BigDecimal";
				case "float": return "Double.parseDouble";//"double";
				//case "image": return "Image";
				case "int": return "Integer.parseInt";// "int"; 
				case "money": return "Double.parseDouble";//"double"; 
				case "nchar": return  "";//"String";
				case "ntext": return  "(byte[])";//"byte[]";
				//case "numeric": return "getBigDecimal";//"BigDecimal";
				case "nvarchar": return  "";//"String";
				case "real": return  "Float.parseFloat";//"float";
				//case "smalldatetime": return "getTime";//"Time";
				case "smallint": return "Short.parseShort";//"short";
				case "smallmoney": return  "Float.parseFloat";//"float";
				//case "sql_variant": return "getObject";//"Object";
				case "sysname": return  "";//"String";
				case "text": return  "(byte[])";//"byte[]";
				//case "timestamp": return "getTimestamp";//"Timestamp";
				case "tinyint": return "Byte.parseByte";//"byte";
				case "uniqueidentifier": return  "";//"String";
				case "varbinary": return  "(byte[])";//"byte[]";
				case "varchar": return  "";//"String";
				default: return "__UNKNOWN__" + column.NativeType;
			}
		}
		#endregion
		
		#region 返回表的第一个主键列 及字段名、名称、类型
		
		//返回表的第一个主键列
		public DataObjectBase GetFirstPrimaryCol(TableSchema srcTable)
		{
			return srcTable.PrimaryKey.MemberColumns[0];			
		}
		
		//返回表的第一个主键列的字段名
		public string GetFirstPrimaryColString(TableSchema srcTable)
		{
			return srcTable.PrimaryKey.MemberColumns[0].Name;
		}
		
		//返回表的第一个主键列的列名
		public string GetFirstPrimaryColName(TableSchema srcTable)
		{
			return GetColumnName(srcTable.PrimaryKey.MemberColumns[0]);
		}
		
		//返回表的第一个主键列的小写列名
		public string GetFirstPrimaryLowColName(TableSchema srcTable)
		{
			return GetLowColumnName(srcTable.PrimaryKey.MemberColumns[0]);
		}
		
		
		//返回表的第一个主键列的类型
		public string GetFirstPrimaryColType(TableSchema srcTable)
		{
			return GetSqlDbType(GetFirstPrimaryCol(srcTable));
		}
		
		//返回主键列的类型和列名
		public string GetFirstPrimaryColWithType(TableSchema srcTable)
		{
			return GetFirstPrimaryColType(srcTable)+" "+GetFirstPrimaryLowColName(srcTable);	
		}
		
		#endregion
		
		#region 返回表主键列 字段名、名称、类型
		
		//返回表的第一个主键列
		public String GetPriColsString(TableSchema srcTable)
		{
			StringBuilder sb = new StringBuilder();
			for(int i=0,len=srcTable.PrimaryKey.MemberColumns.Count;i<len;i++)
			{
				DataObjectBase col = srcTable.PrimaryKey.MemberColumns[i];
				sb.Append(GetLowColumnName(col));
		 		if(i<len-1)
				{
					sb.Append(",");
				}
			}
			return sb.ToString();		
		}
		
		//返回表的第一个主键列
		public String GetPriColsWithType(TableSchema srcTable)
		{
			StringBuilder sb = new StringBuilder();
			for(int i=0,len=srcTable.PrimaryKey.MemberColumns.Count;i<len;i++)
			{
				DataObjectBase col = srcTable.PrimaryKey.MemberColumns[i];
	 			sb.Append(GetSqlDbType(col));
				sb.Append(" ");
				sb.Append(GetLowColumnName(col));
		 		if(i<len-1)
				{
					sb.Append(",");
				}
			}
			return sb.ToString();		
		}
		
		//返回表的第一个主键列
		public String GetPriColsWithTable(TableSchema srcTable)
		{
			StringBuilder sb = new StringBuilder();
			for(int i=0,len=srcTable.PrimaryKey.MemberColumns.Count;i<len;i++)
			{
				DataObjectBase col = srcTable.PrimaryKey.MemberColumns[i];
				sb.Append(GetLowTableName(srcTable));
				sb.Append(".get");
				sb.Append(GetColumnName(col));
				sb.Append("()");
		 		if(i<len-1)
				{
					sb.Append(",");
				}
			}
			return sb.ToString();		
		}
					
		#endregion
		
		#region 返回列名
		

		//返回首字母大写的列名
		public string GetColumnName(DataObjectBase column)	
		{
			return  GetPascalCaseName(column.Name);
		}
		
		//返回首字母小写的列名
		public string GetLowColumnName(DataObjectBase column)	
		{
           return GetCamelCaseName(column.Name);
		}
		
		//返回首字母小写的列名
		public string GetColumnDesc(DataObjectBase column)	
		{
		   String desc = column.Description;
		   if(String.IsNullOrEmpty(desc)){return "";}
		
		   Regex regex = new Regex(@"[\s\(（\,，-]"); 
		   Match m = regex.Match(desc);   
		   int pos = m.Index;
		   if(pos>0){
			  desc = desc.Substring(0,pos);
		   }
		   return desc;
		}
	
		#endregion
		
		#region 返回sql语句中的参数?　及列名
		
		//返回所有列的?
		public string GetAllques(TableSchema srcTable)
		{
			string sql="";
			for(int i=0;i<srcTable.Columns.Count;i++)
			{
				if(i<srcTable.Columns.Count-1)
				{
					sql+="?"+",";					
				}
				else
				{
					sql+="?";					
				}
			}	
			return sql;
		}
		
		//返回非主键列的?
		public string GetNonPriques(TableSchema srcTable)
		{
			string sql="";
			for(int i=0;i<srcTable.NonPrimaryKeyColumns.Count;i++)
			{
				if(i<srcTable.NonPrimaryKeyColumns.Count-1)
				{
					sql+="?"+",";					
				}
				else
				{
					sql+="?";					
				}
			}	
			return sql;
		}
		
		//返回所有列的列名
		public string GetsqlAllCols(TableSchema srcTable)
		{
			string sql="";
			for(int i=0;i<srcTable.Columns.Count;i++)
			{
				if(i<srcTable.Columns.Count-1)
				{
					sql+=srcTable.Columns[i].Name+",";					
				}
				else
				{
					sql+=srcTable.Columns[i].Name;					
				}
			}	
			return sql;
		}
		
		//返回非主键列的列名
		public string GetsqlNonPriCols(TableSchema srcTable)
		{
			string sql="";
			for(int i=0;i<srcTable.NonPrimaryKeyColumns.Count;i++)
			{
				if(i<srcTable.NonPrimaryKeyColumns.Count-1)
				{
					sql+=srcTable.NonPrimaryKeyColumns[i].Name+",";					
				}
				else
				{
					sql+=srcTable.NonPrimaryKeyColumns[i].Name;					
				}
			}
			return sql;
		}
		
		//返回非主键列的列名 ,加上表名前缀
		public string GetsqlNonPriColsT(TableSchema srcTable)
		{
			string sql="";
			for(int i=0;i<srcTable.NonPrimaryKeyColumns.Count;i++)
			{
				if(i<srcTable.NonPrimaryKeyColumns.Count-1)
				{
					sql+=srcTable.Name+"."+srcTable.NonPrimaryKeyColumns[i].Name+",";					
				}
				else
				{
					sql+=srcTable.Name+"."+srcTable.NonPrimaryKeyColumns[i].Name;					
				}
			}
			return sql;
		}
		
	　　//返回Update的列名
		public string GetUpdateCols(TableSchema srcTable)
		{
			string sql="";
			for(int i=0;i<srcTable.NonPrimaryKeyColumns.Count;i++)
			{
				if(i<srcTable.NonPrimaryKeyColumns.Count-1)
				{
					sql+=srcTable.NonPrimaryKeyColumns[i].Name+"=?,";					
				}
				else
				{
					sql+=srcTable.NonPrimaryKeyColumns[i].Name+"=?";					
				}
			}
			return sql;
		}
		#endregion		
			
		#region	返回增删改查的sql语句
				
		//获取从表中选取所有字段的sql语句
		public string GetSelectAllSql(TableSchema srcTable)
		{
			StringBuilder sb=new StringBuilder();
			sb.Append("SELECT ");
			
			//从表中获得所有字段的字段名，以逗号分隔
			sb.Append(GetsqlAllCols(srcTable));
			sb.Append("  FROM ");
			sb.Append(srcTable.Name);
			return sb.ToString();
		}
		
		//获取从ID查询sql语句
		public string GetSelectByIDSql(TableSchema srcTable)
		{
			StringBuilder sb=new StringBuilder();
		    sb.Append(GetSelectAllSql(srcTable));
			sb.Append(" WHERE ");
			sb.Append(GetFirstPrimaryColString(srcTable));
			sb.Append("=");
			return sb.ToString();
		}
		
		//获取插入所有字段的sql语句
		public string GetInsertAllSql(TableSchema srcTable)
		{
			StringBuilder sb=new StringBuilder();
			sb.Append("INSERT INTO ");
			sb.Append(srcTable.Name);
			sb.Append(" (");
			
			//从表中获得所有字段的字段名，以逗号分隔
			sb.Append(GetsqlAllCols(srcTable));
			sb.Append(") VALUES(");
			sb.Append(GetAllques(srcTable));
			sb.Append(") ");
 			return sb.ToString();
		}
		
		//获得计算数据行的sql语句
		public string GetCountSql(TableSchema srcTable)
		{
			StringBuilder sb=new StringBuilder();
			sb.Append("SELECT COUNT(1) FROM ");
			sb.Append(srcTable.Name);
			return sb.ToString();
		}
		
		//获得更新的sql语句
		public string GetUpdateSql(TableSchema srcTable)
		{
			StringBuilder sb=new StringBuilder();
			sb.Append("UPDATE ");
			sb.Append(srcTable.Name);
			sb.Append(" SET ");
			sb.Append(GetUpdateCols(srcTable));
			sb.Append("  WHERE ");
			sb.Append(GetFirstPrimaryColString(srcTable));
			sb.Append("=?");
 			return sb.ToString();
		}

　　　　 #endregion	
		
 		#region 返回ec:table 的列宽
		
		public string GetWidth(TableSchema srcTable)
		{
			int i=(100/(srcTable.NonPrimaryKeyColumns.Count+1));
			return i.ToString();
			
		}
		
		#endregion
		
		#region  返回版权信息
	
		//返回版权信息
		public string GetCopyright(string project,string author,string email)
		{
			StringBuilder sb=new StringBuilder();
			sb.Append("/*\n");
			sb.Append("* Project: "+project+"\n");
			sb.Append("* Copyright(c)\n");
			sb.Append("* Version: 1.0.0 \n");
			sb.Append("* Author: "+author +" <"+email+"> \n");
			sb.Append("* DateTime: "+GetTimeCN()+"\n");
			sb.Append("*/\n");
			return sb.ToString();
    	}
		#endregion
		
		#region GetParent/Child Tables
		///<summary>
		/// Get's the parent tables if any based on a child table.
		///</summary>
		public TableSchemaCollection GetParentTables(SchemaExplorer.TableSchema table)
		{
			TableSchemaCollection _tbParent= new TableSchemaCollection();

			DatabaseSchema _dbCurrent;
			_dbCurrent=table.Database;
			
			foreach(TableSchema _tb in _dbCurrent.Tables){
				if(table.Name!=_tb.Name){
					foreach(ColumnSchema col in table.Columns){
						if (col.Name == _tb.PrimaryKey.MemberColumns[0].Name){
							_tbParent.Add(_tb);
						}
					}                        
					 
				}
			}
			return _tbParent;
		}
			
		///<summary>
		///  Get's all the child tables based on a parent table
		///</summary>
		public TableSchemaCollection GetChildTables(SchemaExplorer.TableSchema table)
		{
			TableSchemaCollection _tbChild= new TableSchemaCollection();

				DatabaseSchema _dbCurrent;
				_dbCurrent=table.Database;
				foreach(TableSchema _tb in _dbCurrent.Tables){
					if(table.Name!=_tb.Name){
						foreach(ColumnSchema _col in _tb.Columns){
							foreach(ColumnSchema primaryCol in table.PrimaryKey.MemberColumns){
								if (_col.Name == primaryCol.Name){
									_tbChild.Add(_tb);
								}
							}                       
						}
					}
				}
			return _tbChild;
		}
		
		public string GetParentPriCols(SchemaExplorer.TableSchema table)
		{
			StringBuilder sb=new StringBuilder();
			TableSchemaCollection _tbParent=GetParentTables(table);
			for(int i=0;i<_tbParent.Count;i++)
			{
				if(i<_tbParent.Count-1)
				{
				  sb.Append("\n"+_tbParent[i].Name+".MING_CHENG AS "+GetShBigTableName(_tbParent[i])+"_NAME,");
				}
				else
				{
				  sb.Append("\n"+_tbParent[i].Name+".MING_CHENG AS "+GetShBigTableName(_tbParent[i])+"_NAME");
				}
			}
			return sb.ToString();
		}
		
		public string GetsqlLeftJoin(SchemaExplorer.TableSchema table)
		{
			StringBuilder sb=new StringBuilder();
			sb.Append(" SELECT ");
			sb.Append(GetsqlNonPriColsT(table));
			sb.Append(", \n");
			sb.Append(GetParentPriCols(table));
			sb.Append("\n FROM "+table.Name );
			
			foreach(TableSchema _tb in GetParentTables(table))
			{
				sb.Append("\n LEFT JOIN "+_tb.Name);
				sb.Append("\n ON "+table.Name +"."+GetFirstPrimaryColString(_tb)+"="+_tb.Name+"."+GetFirstPrimaryColString(_tb)+" ");
			}
			return sb.ToString();
			
		}
		
		//带注释的完整语句
		public string GetsqlLeftJoinC(SchemaExplorer.TableSchema table)
		{
			string plus="+";
			string quote="\"";
			string pq="+\"";
			string nn="\n\"";
			string pqn="\n+\" ";
			string slash=" //";
			string qn="\"\n";
		
			StringBuilder sb=new StringBuilder();
			sb.Append(quote+" SELECT ");
			sb.Append(GetsqlNonPriColsT(table));
			sb.Append(", "+quote+slash+"主表字段");
			
			
			TableSchemaCollection _tbParent=GetParentTables(table);
			for(int i=0;i<_tbParent.Count;i++)
			{
				if(i<_tbParent.Count-1)
				{
				  sb.Append(pqn+_tbParent[i].Name+".MING_CHENG AS "+GetShBigTableName(_tbParent[i])+"_NAME,"+quote+slash+GetTableDesc(_tbParent[i])+"名称");
				}
				else
				{
				  sb.Append(pqn+_tbParent[i].Name+".MING_CHENG AS "+GetShBigTableName(_tbParent[i])+"_NAME"+quote+slash+GetTableDesc(_tbParent[i])+"名称");
				}
			}
			
			sb.Append(pqn+" FROM "+table.Name+qn);
			
			foreach(TableSchema _tb in GetParentTables(table))
			{
				sb.Append(pqn+" LEFT JOIN "+_tb.Name+quote+slash+"左连接 "+GetTableDesc(_tb));
				sb.Append(pqn+" ON "+table.Name +"."+GetFirstPrimaryColString(_tb)+"="+_tb.Name+"."+GetFirstPrimaryColString(_tb)+qn);
			}
			return sb.ToString();
			
		}
		
		#endregion 
		
		#region 返回字段默认空值
	　　//返回字段默认空值
		public string GetJavaDefaultByType(DataObjectBase column)
		{
			//return GetCSDefaultByType(column, false);
			return GetCSDefaultByType(column, true);
		}
		
		
		//返回字段默认空值
	 
		public string GetCSDefaultByType(DataObjectBase column, bool forceReturnDefault)
		{
			// first attempt to see if the DB defines any default values for this
			// column.  If so, return it.
			string defaultValue;
			
			if (!forceReturnDefault)
			{
				defaultValue = GetCSDefaultValueByType(column);
				if (defaultValue != null)
					return defaultValue;
			}

			if (column.NativeType.ToLower() == "real")
				return "0.0";
			else
			{
				DbType dataType = column.DataType;
				switch (dataType)
				{
					case DbType.AnsiString: 
						return "null";
						
					case DbType.AnsiStringFixedLength: 
						return "null";
					
					case DbType.String: 
						return "null";
						
					case DbType.Boolean: 
						return "false";
					
					case DbType.StringFixedLength: 
						return "null";
						
					case DbType.Guid: 
						return "null";
					
					
					//Answer modified was just 0
					case DbType.Binary: 
						return "null";
					
					//Answer modified was just 0
					case DbType.Byte:
						return "0";
						//return "{ 0 }";
					
					case DbType.Currency: 
						return "0";
					
					case DbType.Date: 
						return "null";
					
					case DbType.DateTime: 
						return "null";
					
					case DbType.Decimal: 
						return "0.0";
						//return "0M";
						//return "0.0M";
					
					case DbType.Double: 
						return "0.0";
					
					case DbType.Int16: 
						return "0";
						
					case DbType.Int32: 
						return "0";
						
					case DbType.Int64: 
						return "0";
					
					case DbType.Object: 
						return "null";
					
					case DbType.Single: 
						return "0";
					
					//case DbType.Time: return "DateTime.MaxValue";
					//case DbType.Time: return "new DateTime(1900,1,1,0,0,0,0)";
					case DbType.Time: return "null";
					case DbType.VarNumeric: return "0";
						//the following won't be used
						//		case DbType.SByte: return "0";
						//		case DbType.UInt16: return "0";
						//		case DbType.UInt32: return "0";
						//		case DbType.UInt64: return "0";
					default: return "null";
				}
			}
		}
		
		/// <summary>
		/// This method returns the default value from the database if it is available.  It returns null
		/// if no default value could be parsed.
		/// 
		/// NOTE: rudimentary support for default values with computations/functions is built in.  Right now th
		///   only supported function is getdate().  Any others can be added below if desired.
		/// </summary>
		/// <param name="column"></param>
		public string GetCSDefaultValueByType(DataObjectBase column)
		{
			if (column == null)
				return null;

			ExtendedProperty defaultValueProperty = column.ExtendedProperties["CS_Default"];
			if (defaultValueProperty == null)
				return null;			
				
			string defaultValue = null;
			
			#region Convert declarations
			bool boolConvert;
			byte byteConvert;
			decimal decimalConvert;
			double doubleConvert;
			float floatConvert;
			int intConvert;
			long longConvert;
			short shortConvert;
			DateTime dateConvert;
			Guid guidConvert; 
			XmlNode xmlNodeConvert;
			#endregion
	
			try
			{
				//Get Default Value 
				defaultValue = defaultValueProperty.Value.ToString();
				
				if (defaultValue == null || defaultValue.Trim().Length == 0)
					return null;
				
				// trim off the surrounding ()'s if they exist (SQL Server)
				while (defaultValue.StartsWith("(") && defaultValue.EndsWith(")") 
					|| defaultValue.StartsWith("\"") && defaultValue.EndsWith("\""))
				{
					defaultValue = defaultValue.Substring(1);
					defaultValue = defaultValue.Substring(0, defaultValue.Length - 1);
				}
				
				if (IsNumericType(column as ColumnSchema))
					defaultValue = defaultValue.TrimEnd('.');
					
				if (defaultValueProperty.DataType == DbType.String)
				{
					// probably a char type.  Let's remove the quotes so parsing is happy
					if (defaultValue.StartsWith("'") && defaultValue.EndsWith("'"))
					{
						defaultValue = defaultValue.Substring(1);
						defaultValue = defaultValue.Substring(0, defaultValue.Length - 1);
					}
		
					//this is probably a custom function, lets handle it sane-like
					if (defaultValue.Contains("()"))
					{
						if ( defaultValue.ToLower() == "getdate()" )
							defaultValue = "DateTime.Now";
						else if ( defaultValue.ToLower() == "newid()" )
							defaultValue = "new Guid()";
						else if ( defaultValue.ToLower() == "getutcdate()" )
							defaultValue = "DateTime.UtcNow";
						else
							return null;
					}
				}

				if (column.NativeType.ToLower() == "real")
				{
					floatConvert = float.Parse(defaultValue);
					if (defaultValue != null)
						return floatConvert.ToString() + "F";
					else
						return null;
				}
				else
				{
					DbType dataType = column.DataType;
					switch (dataType)
					{
						case DbType.AnsiString:
							if (defaultValue != null)
								return "\"" + defaultValue + "\"";
							else
								return null;
			
						case DbType.AnsiStringFixedLength:
							if (defaultValue != null)
								return "\"" + defaultValue + "\"";
							else
								return null;
			
						case DbType.String:
							if (defaultValue != null)
								return "\"" + defaultValue + "\"";
							else
								return null;
			
						case DbType.Boolean:
						
							if (defaultValue != null )
							{
								if (defaultValue == "1") return "true";
								if (defaultValue == "0") return "false";
								if (defaultValue.Trim().ToLower() == "yes") return "true";
								if (defaultValue.Trim().ToLower() == "no") return "false";
								if (defaultValue.Trim().ToLower() == "y") return "true";
								if (defaultValue.Trim().ToLower() == "n") return "false";
								
								boolConvert = bool.Parse(defaultValue);
								return boolConvert.ToString();
							}
							else
								return null;
			
						case DbType.StringFixedLength:
							if (defaultValue != null)
								return "\"" + defaultValue + "\"";
							else
								return null;
			
						case DbType.Guid:
							if (defaultValue == "new Guid()")
								return defaultValue;
								
							guidConvert = new Guid(defaultValue);
							if (defaultValue != null && guidConvert != null)
								return "new Guid(\"" + guidConvert.ToString() + "\")";
							else
								return null;
						case DbType.Xml:
								return null;			
			
						//Answer modified was just 0
						case DbType.Binary:
							return null;
			
						//Answer modified was just 0
						case DbType.Byte:
							if (defaultValue != null )
							{
								byteConvert = byte.Parse(defaultValue);
								return "(byte)" + byteConvert.ToString();
							}
							else
								return null;
			
						case DbType.Currency:
							if (defaultValue != null)
							{
								decimalConvert = decimal.Parse(defaultValue);
								return decimalConvert.ToString() + "m";
							}
							else
								return null;
			
						case DbType.Date:
						case DbType.DateTime:
						
							if (defaultValue == "DateTime.Now")
								return "DateTime.Now";
							if (defaultValue == "DateTime.UtcNow")
								return "DateTime.UtcNow";

							dateConvert = DateTime.Parse(defaultValue);
							if (defaultValue != null )
								return "new DateTime(\"" + dateConvert.ToString() + "\")";
					
							return null;
						
						case DbType.Decimal:
							if (defaultValue != null)
							{
								decimalConvert = decimal.Parse(defaultValue);
								return decimalConvert.ToString() + "m";
							}
							else
								return null;
			
						case DbType.Double:
							if (defaultValue != null)
							{
								floatConvert = float.Parse(defaultValue);
								return floatConvert.ToString() + "f";
							}
							else
								return null;
			
						case DbType.Int16:
							if (defaultValue != null)
							{
								shortConvert = short.Parse(defaultValue);
								return "(short)" + shortConvert.ToString();
							}
							else
								return null;
			
						case DbType.Int32:
							if (defaultValue != null )
							{
								intConvert = int.Parse(defaultValue);
								return "(int)" + intConvert.ToString();
							}
							else
								return null;
			
						case DbType.Int64:
							if (defaultValue != null)
							{
								longConvert = long.Parse(defaultValue);
								return "(long)" + longConvert.ToString();
							}
							else
								return null;
			
						case DbType.Object:
							return null;
			
						case DbType.Single:
							if (defaultValue != null)
							{
								floatConvert = float.Parse(defaultValue);
								return floatConvert.ToString() + "F";
							}
							else
								return null;
			
						case DbType.Time:
							if (defaultValue == "DateTime.Now")
								return defaultValue;
							else if (defaultValue != null)
							{
								dateConvert = DateTime.Parse(defaultValue);
								return "DateTime.Parse(\"" + dateConvert.ToString() + "\")";
							}
							return null;
						case DbType.VarNumeric:
							if (defaultValue != null)
							{	
								decimalConvert = decimal.Parse(defaultValue);
								return decimalConvert.ToString();
							}
							else
								return null;
						//the following won't be used
						//		case DbType.SByte: return "0";
						//		case DbType.UInt16: return "0";
						//		case DbType.UInt32: return "0";
						//		case DbType.UInt64: return "0";
						default: return null;
					}
				}
			}
			catch{}
			return null;
		}
      
	    public bool IsNumericType(ColumnSchema column)
	{
			switch (column.NativeType.ToLower())
			{
				case "bigint":
				case "bit":
				case "decimal":
				case "float":
				case "int":
				case "money":
				case "numeric":
				case "real":
				case "smallint":
				case "smallmoney":
				case "tinyint": return true;
				default: return false;
			}
	}
	    #endregion
	
	}
}